USE Fresher14_G1

----Create Procedure----

--sp_GelAllContractType

GO
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='sp_GelAllContractType' AND TYPE ='P')
	DROP PROC sp_GelAllContractType
	GO
	CREATE PROC sp_GelAllContractType
	AS
	BEGIN
		SElECT * FROM ContractType ORDER BY ContractTypeName
	END

---sp_CreateContractType

GO
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='sp_CreateContractType' AND TYPE ='P')
	DROP PROC sp_CreateContractType
	GO
	CREATE PROC sp_CreateContractType
	@ContractTypeName NVARCHAR(30),
	@IsActive BIT
	AS
	INSERT INTO ContractType(ContractTypeName,IsActive) VALUES (@ContractTypeName,@IsActive)
	INSERT INTO SystemEventLog(SystemEventType, Description) VALUES('Infomation', 'Create new contract type')

---sp_CheckContractTypeName

GO
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='sp_CheckContractTypeName' AND TYPE ='P')
	DROP PROC sp_CheckContractTypeName
	GO
	CREATE PROC sp_CheckContractTypeName
	@ContractTypeName NVARCHAR(30)
	AS
	SELECT COUNT(*) FROM ContractType WHERE ContractTypeName = @ContractTypeName

---sp_SetIsActiveContractType

GO
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='sp_SetIsActiveContractType' AND TYPE ='P')
	DROP PROC sp_SetIsActiveContractType
	GO
	CREATE PROC sp_SetIsActiveContractType
	@ContractTypeId UNIQUEIDENTIFIER,
	@IsActive BIT
	AS
	UPDATE ContractType SET IsActive = @IsActive WHERE ContractTypeId = @ContractTypeId
	
---sp_UpdateContractType

GO
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='sp_UpdateContractType' AND TYPE ='P')
	DROP PROC sp_UpdateContractType
	GO
	CREATE PROC sp_UpdateContractType
	@ContractTypeId UNIQUEIDENTIFIER,
	@ContractTypeName NVARCHAR(30),
	@IsActive BIT
	AS
	UPDATE ContractType SET ContractTypeName = @ContractTypeName,IsActive = @IsActive
	WHERE ContractTypeId = @ContractTypeId	
	INSERT INTO SystemEventLog(SystemEventType, Description) VALUES('Infomation', 'Update contract type')

---sp_GetContractTypeById

GO
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='sp_GetContractTypeById' AND TYPE ='P')
	DROP PROC sp_GetContractTypeById
	GO
	CREATE PROC sp_GetContractTypeById
	@ContractTypeId UNIQUEIDENTIFIER
	AS
	SELECT * FROM ContractType WHERE ContractTypeId = @ContractTypeId

---sp_DeleteContractType

GO
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='sp_DeleteContractType' AND TYPE ='P')
	DROP PROC sp_DeleteContractType
	GO
	CREATE PROC sp_DeleteContractType
	@ContractTypeId UNIQUEIDENTIFIER
	AS
	DELETE FROM ContractType WHERE ContractTypeId = @ContractTypeId
	INSERT INTO SystemEventLog(SystemEventType, Description) VALUES('Infomation', 'Delete contract type')

	

